<html>
 <head>
  <meta charset="UTF-8">
 </head>
 <body>
  <h1 data-lake-id="quOR8" id="quOR8"><span data-lake-id="u413c0688" id="u413c0688">典型回答</span></h1>
  <p data-lake-id="u3d7b2364" id="u3d7b2364"><br></p>
  <p data-lake-id="u49dcd772" id="u49dcd772"><span data-lake-id="u60464a1a" id="u60464a1a">SXSSFWorkbook 类是为了处理大型 Excel 文件而设计的。它的实现原理是</span><strong><span data-lake-id="uad8e9854" id="uad8e9854">通过将部分数据写入磁盘上的临时文件来减少内存占用</span></strong><span data-lake-id="u8e7335c8" id="u8e7335c8">。</span></p>
  <p data-lake-id="u04c2d6bc" id="u04c2d6bc"><span data-lake-id="ufdcd211e" id="ufdcd211e">​</span><br></p>
  <p data-lake-id="ua0d9eed1" id="ua0d9eed1"><span data-lake-id="ueedf29aa" id="ueedf29aa">在SXSSFWorkbook类中，有一个类叫做</span><span data-lake-id="u11b47061" id="u11b47061" class="lake-fontsize-11" style="color: rgb(17, 24, 39)">SheetDataWriter，这个类的作用就是将部分数据写入磁盘上的临时文件的。</span></p>
  <p data-lake-id="u85b197d1" id="u85b197d1"><span data-lake-id="u0f71ddec" id="u0f71ddec" class="lake-fontsize-11" style="color: rgb(17, 24, 39)">​</span><br></p>
  <pre lang="java"><code>
public class SXSSFWorkbook implements Workbook {
	protected SheetDataWriter createSheetDataWriter() throws IOException {
        if(_compressTmpFiles) {
            return new GZIPSheetDataWriter(_sharedStringSource);
        }
        
        return new SheetDataWriter(_sharedStringSource);
    }
}
</code></pre>
  <p data-lake-id="u6df9f9f5" id="u6df9f9f5"><br></p>
  <p data-lake-id="u3ffffaff" id="u3ffffaff"><span data-lake-id="u797c4f72" id="u797c4f72">写入过程是在 SheetDataWriter 的 writeRow 方法中实现的。此方法会被 SXSSFSheet 调用，以将行数据转换成 XML 并写入临时文件。</span></p>
  <p data-lake-id="ue60f754a" id="ue60f754a"><span data-lake-id="ufa47aea8" id="ufa47aea8">​</span><br></p>
  <pre lang="java"><code>
public void writeRow(int rownum, SXSSFRow row) throws IOException {
    if (_numberOfFlushedRows == 0)
        _lowestIndexOfFlushedRows = rownum;
    _numberLastFlushedRow = Math.max(rownum, _numberLastFlushedRow);
    _numberOfCellsOfLastFlushedRow = row.getLastCellNum();
    _numberOfFlushedRows++;
    beginRow(rownum, row);
    Iterator&lt;Cell&gt; cells = row.allCellsIterator();
    int columnIndex = 0;
    while (cells.hasNext()) {
        writeCell(columnIndex++, cells.next());
    }
    endRow();
}
</code></pre>
  <p data-lake-id="u80d1af03" id="u80d1af03"><br></p>
  <p data-lake-id="u81dcaff0" id="u81dcaff0"><span data-lake-id="u4533e0d6" id="u4533e0d6">writeRow方法会循环调用writeCell方法：</span></p>
  <p data-lake-id="u24736354" id="u24736354"><span data-lake-id="ub94a88b1" id="ub94a88b1">​</span><br></p>
  <pre lang="java"><code>
public void writeCell(int columnIndex, Cell cell) throws IOException {
    if (cell == null) {
        return;
    }
    String ref = new CellReference(_rownum, columnIndex).formatAsString();
    _out.write("&lt;c");
    writeAttribute("r", ref);
    CellStyle cellStyle = cell.getCellStyle();
    if (cellStyle.getIndex() != 0) {
        // need to convert the short to unsigned short as the indexes can be up to 64k
        // ideally we would use int for this index, but that would need changes to some more
        // APIs
        writeAttribute("s", Integer.toString(cellStyle.getIndex() &amp; 0xffff));
    }
    CellType cellType = cell.getCellType();
    switch (cellType) {
        case BLANK: {
            _out.write('&gt;');
            break;
        }
        case FORMULA: {
            switch(cell.getCachedFormulaResultType()) {
                case NUMERIC:
                    writeAttribute("t", "n");
                    break;
                case STRING:
                    writeAttribute("t", STCellType.STR.toString());
                    break;
                case BOOLEAN:
                    writeAttribute("t", "b");
                    break;
                case ERROR:
                    writeAttribute("t", "e");
                    break;
            }
            _out.write("&gt;&lt;f&gt;");
            outputQuotedString(cell.getCellFormula());
            _out.write("&lt;/f&gt;");
            switch (cell.getCachedFormulaResultType()) {
                case NUMERIC:
                    double nval = cell.getNumericCellValue();
                    if (!Double.isNaN(nval)) {
                        _out.write("&lt;v&gt;");
                        _out.write(Double.toString(nval));
                        _out.write("&lt;/v&gt;");
                    }
                    break;
                case STRING:
                    String value = cell.getStringCellValue();
                    if(value != null &amp;&amp; !value.isEmpty()) {
                        _out.write("&lt;v&gt;");
                        _out.write(value);
                        _out.write("&lt;/v&gt;");
                    }
                    break;
                case BOOLEAN:
                    _out.write("&gt;&lt;v&gt;");
                    _out.write(cell.getBooleanCellValue() ? "1" : "0");
                    _out.write("&lt;/v&gt;");
                    break;
                case ERROR: {
                    FormulaError error = FormulaError.forInt(cell.getErrorCellValue());

                    _out.write("&gt;&lt;v&gt;");
                    _out.write(error.getString());
                    _out.write("&lt;/v&gt;");
                    break;
                }
            }
            break;
        }
        case STRING: {
            if (_sharedStringSource != null) {
                XSSFRichTextString rt = new XSSFRichTextString(cell.getStringCellValue());
                int sRef = _sharedStringSource.addSharedStringItem(rt);

                writeAttribute("t", STCellType.S.toString());
                _out.write("&gt;&lt;v&gt;");
                _out.write(String.valueOf(sRef));
                _out.write("&lt;/v&gt;");
            } else {
                writeAttribute("t", "inlineStr");
                _out.write("&gt;&lt;is&gt;&lt;t");
                if (hasLeadingTrailingSpaces(cell.getStringCellValue())) {
                    writeAttribute("xml:space", "preserve");
                }
                _out.write("&gt;");
                outputQuotedString(cell.getStringCellValue());
                _out.write("&lt;/t&gt;&lt;/is&gt;");
            }
            break;
        }
        case NUMERIC: {
            writeAttribute("t", "n");
            _out.write("&gt;&lt;v&gt;");
            _out.write(Double.toString(cell.getNumericCellValue()));
            _out.write("&lt;/v&gt;");
            break;
        }
        case BOOLEAN: {
            writeAttribute("t", "b");
            _out.write("&gt;&lt;v&gt;");
            _out.write(cell.getBooleanCellValue() ? "1" : "0");
            _out.write("&lt;/v&gt;");
            break;
        }
        case ERROR: {
            FormulaError error = FormulaError.forInt(cell.getErrorCellValue());

            writeAttribute("t", "e");
            _out.write("&gt;&lt;v&gt;");
            _out.write(error.getString());
            _out.write("&lt;/v&gt;");
            break;
        }
        default: {
            throw new IllegalStateException("Invalid cell type: " + cellType);
        }
    }
    _out.write("&lt;/c&gt;");
}
</code></pre>
  <p data-lake-id="u1a9f85c3" id="u1a9f85c3"><br></p>
  <p data-lake-id="u10aefef4" id="u10aefef4"><span data-lake-id="u4c4f15e7" id="u4c4f15e7">在这个方法中，数据会在 _out.write(...) 调用时写入磁盘，这里的_out其实就是一个写入磁盘文件的Writer，他的write方法就会把内容写入到临时文件中。</span></p>
  <p data-lake-id="uc8778581" id="uc8778581"><span data-lake-id="u2dfec5a4" id="u2dfec5a4">​</span><br></p>
  <p data-lake-id="u3c786547" id="u3c786547"><span data-lake-id="u9a28cafc" id="u9a28cafc">我尝试着在_out初始化的地方，也就是：</span></p>
  <p data-lake-id="u9651769b" id="u9651769b"><span data-lake-id="ucf1789c6" id="ucf1789c6">​</span><br></p>
  <pre lang="java"><code>
public SheetDataWriter() throws IOException {
    _fd = createTempFile();
    _out = createWriter(_fd);
}
</code></pre>
  <p data-lake-id="u5c1354fb" id="u5c1354fb"><br></p>
  <p data-lake-id="u14ac5990" id="u14ac5990"><span data-lake-id="u695e954b" id="u695e954b">中加了断点，就能在运行过程中找到这个临时文件，tail一下临时文件就会发现他不断地在有文件写入。</span></p>
  <p data-lake-id="u72849b6c" id="u72849b6c"><span data-lake-id="uee7c128a" id="uee7c128a">​</span><br></p>
  <p data-lake-id="u33af2806" id="u33af2806"><img src="https://cdn.nlark.com/yuque/0/2023/png/5378072/1700376924337-169f9616-ab5a-4ec6-a94a-0123000536ec.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_36%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="u58e4159d" id="u58e4159d"><br></p>
  <p data-lake-id="uc66602e6" id="uc66602e6"><span data-lake-id="u9970f7d6" id="u9970f7d6">感兴趣的也可以debug看一下这个临时文件的内容，其实他就是一个xml文件，然后写入的就是我们excel中的内容。</span></p>
  <p data-lake-id="u7d6e86f8" id="u7d6e86f8"><span data-lake-id="u45b7fcd5" id="u45b7fcd5">​</span><br></p>
  <p data-lake-id="u68c386d5" id="u68c386d5"><img src="https://cdn.nlark.com/yuque/0/2023/png/5378072/1700376941273-908b7369-ec83-4c16-a951-3754d41897a0.png?x-oss-process=image%2Fwatermark%2Ctype_d3F5LW1pY3JvaGVp%2Csize_27%2Ctext_SmF2YSA4IEd1IFA%3D%2Ccolor_FFFFFF%2Cshadow_50%2Ct_80%2Cg_se%2Cx_10%2Cy_10"></p>
  <p data-lake-id="u8165e05b" id="u8165e05b"><br></p>
  <p data-lake-id="u1e0d1d38" id="u1e0d1d38"><strong><span data-lake-id="u0d728db4" id="u0d728db4">所以，在SXSSFWorkbook中，我们在写入文件时，并不是把所有内容都暂留在内存内，而是会把部分数据写入临时文件，来减少对内存的占用，内存中只保留当前的一部分数据，这样就可以避免内存溢出的问题了、</span></strong></p>
  <p data-lake-id="u694b2749" id="u694b2749"><span data-lake-id="ue4f96c48" id="ue4f96c48">​</span><br></p>
  <h1 data-lake-id="HE187" id="HE187"><span data-lake-id="ue5611200" id="ue5611200">扩展知识</span></h1>
  <p data-lake-id="u613b516d" id="u613b516d"><br></p>
  <h2 data-lake-id="rt8jV" id="rt8jV"><span data-lake-id="u931402ab" id="u931402ab">配置行缓存限制</span></h2>
  <p data-lake-id="ua230a1b8" id="ua230a1b8"><br></p>
  <p data-lake-id="u5ebde4c9" id="u5ebde4c9"><span data-lake-id="ua94d3125" id="ua94d3125">我们可以主动设置行缓存限制，超过这个限制的数据将被写入磁盘上的临时文件。在创建SXSSFWorkbook的时候，可以指定rowAccessWindowSize来实现。</span></p>
  <p data-lake-id="u14d2223b" id="u14d2223b"><br></p>
  <pre lang="java"><code>
  /**
   * Construct an empty workbook and specify the window for row access.
   * &lt;p&gt;
   * When a new node is created via {@link SXSSFSheet#createRow} and the total number
   * of unflushed records would exceed the specified value, then the
   * row with the lowest index value is flushed and cannot be accessed
   * via {@link SXSSFSheet#getRow} anymore.
   * &lt;/p&gt;
   * &lt;p&gt;
   * A value of &lt;code&gt;-1&lt;/code&gt; indicates unlimited access. In this case all
   * records that have not been flushed by a call to &lt;code&gt;flush()&lt;/code&gt; are available
   * for random access.
   * &lt;/p&gt;
   * &lt;p&gt;
   * A value of &lt;code&gt;0&lt;/code&gt; is not allowed because it would flush any newly created row
   * without having a chance to specify any cells.
   * &lt;/p&gt;
   *
   * @param rowAccessWindowSize the number of rows that are kept in memory until flushed out, see above.
   */
  public SXSSFWorkbook(int rowAccessWindowSize){
    this(null /*workbook*/, rowAccessWindowSize);
  }

</code></pre>
 </body>
</html>